package com.yjm.test.Spring5.ch6.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.support.GenericApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.yjm.test.Spring5.ch6.dao.SingerDao;
import com.yjm.test.Spring5.ch6.entities.Album;
import com.yjm.test.Spring5.ch6.entities.Singer;

@Repository("NamedParameterSingerDao")
public class JdbcNamedParameterSingerDao implements SingerDao, InitializingBean {
	private DataSource dataSource;
	private NamedParameterJdbcTemplate jdbcTemplate;
	
	@Autowired
	void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
	}
	
	@Override
	public void afterPropertiesSet() throws Exception {
		// TODO Auto-generated method stub

	}
	
	private static final class SingerMapper implements RowMapper<Singer> {
		@Override
		public Singer mapRow(ResultSet rs, int rowNum) throws SQLException {
			Singer singer = new Singer();
			singer.setId(rs.getLong("id"));
			singer.setFirstName(rs.getString("first_name"));
			singer.setLastName(rs.getString("last_name"));
			singer.setBirthDate(rs.getDate("birth_date"));
			return singer;
		}
	}

	@Override
	public List<Singer> findAll() {
		String sql = "select * from SINGER ";
		return jdbcTemplate.query(sql, new SingerMapper());
	}

	@Override
	public List<Singer> findByFirstName(String firstName) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String findLastNameById(Long id) {
		String sql = "select last_name from SINGER where id=:singerId";
		Map<String,Object> namedParameters = new HashMap<>();
		namedParameters.put("singerId", id);
		return jdbcTemplate.queryForObject(sql, namedParameters, String.class);
	}

	@Override
	public String findFirstNameById(Long id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void insert(Singer singer) {
		// TODO Auto-generated method stub

	}

	@Override
	public boolean update(Singer singer) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean delete(Long singerId) {
		// TODO Auto-generated method stub
		return false;
	}

	private static final class SingerWithDetailExtractor implements ResultSetExtractor<List<Singer>> {

		@Override
		public List<Singer> extractData(ResultSet rs) throws SQLException, DataAccessException {
			Map<Long, Singer> map = new HashMap<>();
			Singer singer;
			while (rs.next()) {
				Long id = rs.getLong("id");
				singer = map.get(id);
				if (singer == null) {
					singer = new Singer();
					singer.setId(rs.getLong("id"));
					singer.setFirstName(rs.getString("first_name"));
					singer.setLastName(rs.getString("last_name"));
					singer.setBirthDate(rs.getDate("birth_date"));
					map.put(id, singer);
				}
				Long albumId = rs.getLong("album_id");
				if (albumId > 0) {
					Album album = new Album();
					album.setId(albumId);
					album.setSingerId(id);
					album.setTitle(rs.getString("title"));
					album.setReleaseDate(rs.getDate("release_date"));
					singer.addAlbum(album);
				}
			}
			
			return new ArrayList<>(map.values());
		}
	}
	@Override
	public List<Singer> findAllWithDetail() {
		String sql = "select s.id, s.first_name, s.last_name, s.birth_date, "
				+ "a.id as album_id, a.title as title, a.release_date as release_date from SINGER s "
				+ "left join ALBUM a on s.id = a.singer_id";
		return jdbcTemplate.query(sql, new SingerWithDetailExtractor());
	}

	@Override
	public void insertWithDetail(Singer singer) {
		// TODO Auto-generated method stub

	}
	
	@Configuration
	@PropertySource("classpath:db/jdbc.properties")
	@ComponentScan(basePackages="com.yjm.test.Spring5.ch6.dao.impl")
	public static class AppConfig {
		@Value("${jdbc.driverClassName}")
		private String driverClassName;
		@Value("${jdbc.url}")
		private String url;
		@Value("${jdbc.username}")
		private String username;
		@Value("${jdbc.password}")
		private String password;
		
		@Lazy
		@Bean
		public DataSource dataSource() {
			BasicDataSource dataSource = new BasicDataSource();
			dataSource.setDriverClassName(driverClassName);
			dataSource.setUrl(url);
			dataSource.setUsername(username);
			dataSource.setPassword(password);
			return dataSource;
		}
	}

	public static void main(String[] args) {
		GenericApplicationContext ctx = new AnnotationConfigApplicationContext(AppConfig.class);
		SingerDao singerDao = ctx.getBean("NamedParameterSingerDao", SingerDao.class);
		
		System.out.println("id:" + singerDao.findLastNameById((long) 1));
		System.out.println("findAll-------------");
		List<Singer> singers = singerDao.findAll();
		for (Singer singer:singers) {
			System.out.println(singer.toString());
		}
		System.out.println("findAllWithDetail-------------");
		singers = singerDao.findAllWithDetail();
		for (Singer singer:singers) {
			System.out.println(singer.toString());
		}
		
		
		
		
		
		ctx.close();
	}

}
